package com.wap.framework.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.wap.framework.model.PVCounter;

public class PVCounterDAOImpl implements PVCounterDAO {

	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	private static String getPVcounterById = "SELECT * FROM pv_counter WHERE  id =  ";

	@SuppressWarnings("unchecked")
	public List<PVCounter> listPVByDate(String dateStr) {
		String sql = "SELECT a.*,b.id as joinid ,b.title FROM ("
				+ "select * from pv_counter  where datediff(day,start_time, '"
				+ dateStr
				+ "')=0  and vname like '%DYN_PAGE_%||%'"
				+ ") as a left join dyn_page b on a.vname like N'%DYN_PAGE_'+ convert(nvarchar, b.id )+'||%' order by a.pv desc";
		List<PVCounter> PVCounterList = jdbcTemplate.query(sql,
				new PVCounterRowMapper());
		if (PVCounterList == null || PVCounterList.size() == 0) {
			return null;
		}
		return PVCounterList;
	}

	@SuppressWarnings("unchecked")
	public List<PVCounter> listADClikeByDate(String dateStr) {
		String sql = "SELECT a.*,b.id as joinid ,b.title FROM ("
				+ "select * from pv_counter  where datediff(day,start_time, '"
				+ dateStr
				+ "')=0  and vname like '%AD_CLICK_%||%'"
				+ ") as a left join wap_ad b on a.vname like N'%AD_CLICK_'+ convert(nvarchar, b.id )+'||%' order by a.pv desc";

		List<PVCounter> PVCounterList = jdbcTemplate.query(sql,
				new PVCounterRowMapper());
		if (PVCounterList == null || PVCounterList.size() == 0) {
			return null;
		}
		return PVCounterList;
	}

	@SuppressWarnings("unchecked")
	public PVCounter getPVcounterById(int id) {
		List<PVCounter> PVCounterList = jdbcTemplate.query(getPVcounterById
				+ "'" + String.valueOf(id) + "'", new PVCounterRowMapper());
		if (PVCounterList == null || PVCounterList.size() == 0) {
			return null;
		}
		return (PVCounter) PVCounterList.get(0);
	}

	class PVCounterRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			PVCounter pvc = new PVCounter();
			pvc.setId(rs.getString("id"));
			pvc.setJoinid(rs.getString("joinid"));
			pvc.setTitle(rs.getString("title"));
			pvc.setPv(rs.getString("pv"));
			pvc.setVname(rs.getString("vname"));			
			pvc.setStartTime(rs.getDate("start_time"));
			pvc.setLastTime(rs.getDate("last_time"));
			return pvc;
		}
	}
}
